﻿using System;
using System.Data;
using System.Data.SQLite;

public class GetDataFromSqlLite
{
    public class SourceCode
    {
        public string jysr { set; get; }
        public string jysc { set; get; }
        public string jyname { set; get; }
        public string jydesc { set; get; }
        public string jysr_param { set; get; }
        public string jysc_param { set; get; }
    }

    public static SourceCode GetSourceCode(string jybh)
    {
        SourceCode sourceCode = new SourceCode();
        string connectionString = "Data Source=ybsi.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM InterfaceParam where jybh = @jybh";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                command.Parameters.AddWithValue("@jybh", jybh);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        sourceCode.jysr = Convert.ToString(reader["jysr"]);
                        sourceCode.jysc = Convert.ToString(reader["jysc"]);
                        sourceCode.jyname = Convert.ToString(reader["jyname"]);
                        sourceCode.jydesc = Convert.ToString(reader["jydesc"]);
                        sourceCode.jysr_param = Convert.ToString(reader["jysr_param"]);
                        sourceCode.jysc_param = Convert.ToString(reader["jysc_param"]);
                    }
                }
            }
        }
        return sourceCode;
    }

    public static DataTable GetSourceCode()
    {
        DataTable dataTable = new DataTable();
        string connectionString = "Data Source=ybsi.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM InterfaceParam";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                {
                    adapter.Fill(dataTable);
                }
            }
        }
        return dataTable;
    }

    public static bool SetSourceCode(string jybh, string jysr, string jysc,string jyname,string jydesc,string jysr_param,string jysc_param)
    {
        bool flag = false;
        string connectionString = "Data Source=ybsi.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string query = @"INSERT INTO InterfaceParam (jybh, jysr, jysc,jyname,jydesc,jysr_param,jysc_param)
                VALUES (@jybh, @jysr, @jysc,@jyname, @jydesc, @jysr_param, @jysc_param )
                ON CONFLICT(jybh) 
                DO UPDATE SET 
                    jysr = excluded.jysr,
                    jysc = excluded.jysc,
                    jyname = excluded.jyname,
                    jydesc = excluded.jydesc,
                    jysr_param = excluded.jysr_param,
                    jysc_param = excluded.jysc_param;";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                command.Parameters.AddWithValue("@jybh", jybh);
                command.Parameters.AddWithValue("@jysr", jysr);
                command.Parameters.AddWithValue("@jysc", jysc);
                command.Parameters.AddWithValue("@jyname", jyname);
                command.Parameters.AddWithValue("@jydesc", jydesc);
                command.Parameters.AddWithValue("@jysr_param", jysr_param);
                command.Parameters.AddWithValue("@jysc_param", jysc_param);
                int result = command.ExecuteNonQuery();
                if (result > 0)
                {
                    flag = true;
                }
            }
        }
        return flag;
    }
}

